package com.arethusa;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.swing.JOptionPane;

import com.arethusa.langebaandb.StatistiekenperprogrammaBean;

import simpleorm.core.SConnection;

public class ExportBoekje {

	static int	NORMAL = 0;
	static int	HEADER_ROW = 1;
	static int	FIRST_ROW = 2;
	static int 	SUM_ROW = 3;
	
	String row = null;
	String document = new String(
			"<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>"+
			"<?mso-application progid=\"Word.Document\"?>" +
			"<w:wordDocument" +
		    " xmlns:w = \"http://schemas.microsoft.com/office/word/2003/wordml\"" +
		    " xmlns:v = \"urn:schemas-microsoft-com:vml\"" +
		    " xmlns:w10 = \"urn:schemas-microsoft-com:office:word\"" +
		    " xmlns:sl = \"http://schemas.microsoft.com/schemaLibrary/2003/core\"" +
		    " xmlns:aml = \"http://schemas.microsoft.com/aml/2001/core\"" +
		    " xmlns:wx = \"http://schemas.microsoft.com/office/word/2003/auxHint\"" +
		    " xmlns:o = \"urn:schemas-microsoft-com:office:office\"" +
		    " xmlns:dt = \"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882\"" +
		    " w:macrosPresent=\"no\"" +
		    " w:embeddedObjPresent=\"no\"" +
		    " w:ocxPresent=\"no\"" +
		    " xml:space=\"preserve\"><w:docPr><w:view w:val=\"print\"/></w:docPr>");
	String styleKopje = new String(
			"<w:style w:type=\"paragraph\" w:styleId=\"Kopje\">" +
			"<w:name w:val=\"Kopje\"/>" +
			"<w:basedOn w:val=\"Standaard\"/>" +
			"<w:next w:val=\"Standaard\"/>" +
			"<w:rsid w:val=\"00C64F4B\"/>" +
			"<w:pPr><w:pStyle w:val=\"Kopje\"/><w:spacing w:before=\"360\" w:after=\"240\"/></w:pPr>" +
			"<w:rPr>" +
			"<wx:font wx:val=\"Times New Roman\"/><w:b/><w:sz w:val=\"28\"/>" +
			"</w:rPr>" +
			"</w:style>");
	String pgf2Columns = new String(
			"<w:p><w:pPr><w:sectPr>" +
			"<w:type w:val = \"continuous\"/>"+
			"<w:pgSz w:w=\"11906\" w:h=\"16838\"/>" +
			"<w:pgMar w:top=\"1417\" w:right=\"1417\" w:bottom=\"1417\"" +
			" w:left=\"1417\" w:header=\"720\" w:footer=\"956\" w:gutter=\"0\"/>" +
			"<w:cols w:num=\"2\" w:space=\"709\"/>" +
			"</w:sectPr></w:pPr></w:p>");
	String tblDefinitionDV = new String(
			"<w:tblPr>" +
			"<w:tblW w:w=\"4622\" w:type=\"dxa\"/>" +
			"<w:tblBorders>" +
			"<w:top w:val=\"single\" w:sz=\"12\" wx:bdrwidth=\"30\" w:space=\"0\" w:color=\"008000\"/>" +
			"<w:left w:val=\"nil\"/>" +
			"<w:bottom w:val=\"single\" w:sz=\"12\" wx:bdrwidth=\"30\" w:space=\"0\" w:color=\"008000\"/>" +
			"<w:right w:val=\"nil\"/>" +
			"<w:insideH w:val=\"nil\"/>" +
			"<w:insideV w:val=\"nil\"/>" +
			"</w:tblBorders>" +
			"<w:tblCellMar>" +
			"<w:left w:w=\"70\" w:type=\"dxa\"/>" +
			"<w:right w:w=\"70\" w:type=\"dxa\"/>" +
			"</w:tblCellMar>" +
			"<w:tblLook w:val=\"00BF\"/>" +
			"</w:tblPr>" +
			"<w:tblGrid>" +
			"<w:gridCol w:w=\"1077\"/>" +
			"<w:gridCol w:w=\"2610\"/>" +
			"<w:gridCol w:w=\"935\"/>" +
			"</w:tblGrid>");
	String tblDefinitionPR = new String(
			"<w:tblPr>" +
			"<w:tblW w:w=\"9184\" w:type=\"dxa\"/>" +
			"<w:tblBorders>" +
			"<w:top w:val=\"single\" w:sz=\"12\" wx:bdrwidth=\"30\" w:space=\"0\" w:color=\"808080\"/>" +
			"<w:left w:val=\"nil\"/>" +
			"<w:bottom w:val=\"single\" w:sz=\"12\" wx:bdrwidth=\"30\" w:space=\"0\" w:color=\"808080\"/>" +
			"<w:right w:val=\"nil\"/>" +
			"<w:insideH w:val=\"nil\"/>" +
			"<w:insideV w:val=\"nil\"/>" +
			"</w:tblBorders>" +
			"<w:tblCellMar>" +
			"<w:left w:w=\"70\" w:type=\"dxa\"/>" +
			"<w:right w:w=\"70\" w:type=\"dxa\"/>" +
			"</w:tblCellMar>" +
			"<w:tblLook w:val=\"00B7\"/>" +
			"</w:tblPr>" +
			"<w:tblGrid>" +
			"<w:gridCol w:w=\"1247\"/>" +
			"<w:gridCol w:w=\"3118\"/>" +
			"<w:gridCol w:w=\"1134\"/>" +
			"<w:gridCol w:w=\"850\"/>" +
			"<w:gridCol w:w=\"2835\"/>" +
			"</w:tblGrid>");
	String sectPrPR = new String(
			"<w:sectPr>" +
			"<w:pgSz w:w=\"11906\" w:h=\"16838\"/>" +
			"<w:pgMar w:top=\"1417\" w:right=\"1417\" w:bottom=\"1417\" w:left=\"1417\" w:header=\"708\" w:footer=\"708\" w:gutter=\"0\"/>" +
			"<w:cols w:space=\"708\"/><w:docGrid w:line-pitch=\"360\"/>" +
			"</w:sectPr>");

	public ExportBoekje() {
	}
	
	public void exportDeelnemendeVerenigingen() {
		try {
			File f = new File("Wedstrijdboekje\\Wedstrijdboekje.xml");
			FileOutputStream fs = new FileOutputStream(f);
			fs.write(document.getBytes());
			fs.write("<w:styles>".getBytes());
			fs.write(styleKopje.getBytes());
			fs.write("</w:styles>".getBytes());
			fs.write("<w:body>".getBytes());
			fs.write("<wx:sect>".getBytes());
			fs.write("<w:p>".getBytes());
			fs.write("<w:pPr><w:pStyle w:val=\"Kopje\"/></w:pPr>".getBytes());
			fs.write("<w:r><w:t>Deelnemende Verenigingen</w:t></w:r>".getBytes());
			fs.write("</w:p>".getBytes());
			fs.write(pgf2Columns.getBytes());
			//start table
			fs.write("<w:tbl>".getBytes());
			fs.write(tblDefinitionDV.getBytes());
			//header row
			fs.write(rowDV(HEADER_ROW, "Depot","Vereniging", "Aantal" ).getBytes());
			//execute query
			boolean hasBegun = SConnection.getConnection().hasBegun();
			if( ! hasBegun ) { SConnection.begin(); }
			Connection JDBCcon = SConnection.getBegunDBConnection();
			try {
				boolean first = true;
				Statement stmt = JDBCcon.createStatement();
				ResultSet res = stmt.executeQuery("SELECT Depotnr, Vereniging, Count(*) as nr FROM Start" +
						"  GROUP BY Depotnr, Vereniging ORDER BY Vereniging");
				//start of data rows
				while( res.next() ) {
					if( first ) {
						fs.write(rowDV(FIRST_ROW, res.getString("Depotnr"),cvtString(res.getString("Vereniging")), res.getString("nr") ).getBytes());
						first = false;
					} else {
						fs.write(rowDV(NORMAL, res.getString("Depotnr"),cvtString(res.getString("Vereniging")), res.getString("nr") ).getBytes());
					}
				}
				fs.write(rowDV(SUM_ROW, "","Totaal","0" ).getBytes());
				
			} catch (SQLException e) {
				JOptionPane.showMessageDialog(null,
						e.getMessage()+e.getCause(),
						"SQL fout", JOptionPane.ERROR_MESSAGE);
			}
			if( ! hasBegun ) { SConnection.commit(); }
			//end of data rows
			fs.write("</w:tbl>".getBytes());
			//end of table
			fs.write(pgf2Columns.getBytes());
			fs.write("</wx:sect>".getBytes());
			//program data
			fs.write("<wx:sect>".getBytes());
			// table
			hasBegun = SConnection.getConnection().hasBegun();
			if( ! hasBegun ) { SConnection.begin(); }
			JDBCcon = SConnection.getBegunDBConnection();
			try {
				boolean firstTable = true;
				boolean firstRow = true;
				int prn = 0;
				String sfx = null;
				Statement stmt = JDBCcon.createStatement();
				ResultSet res = stmt.executeQuery(
						"SELECT a.Prognr as Prog, a.Suffix as Suf, CONCAT(b.CapKleur,' ',CapNr) as Cap, " +
						" Naam, Startnr, DepotNr, Vereniging " +
						" FROM Programma a, start b "+
						" WHERE a.ProgNr=b.ProgNr AND a.Suffix=b.Suffix " +
						" ORDER BY a.ProgNr, a.Suffix, CapNr");
				//start of data rows
				while( res.next() ) {
					if( !((prn == res.getInt("Prog")) &&
						(sfx.equalsIgnoreCase(res.getString("Suf")))) ) {
						if( ! firstTable ) fs.write("</w:tbl>".getBytes());
						firstTable = false;
						prn = res.getInt("Prog");
						sfx = res.getString("Suf");
						firstRow = true;
						fs.write(this.printHeader(new Integer(prn).toString(), sfx).getBytes());
						//header row
						fs.write("<w:tbl>".getBytes());
						fs.write(tblDefinitionPR.getBytes());
						fs.write(rowPR(HEADER_ROW, "Cap", "Naam", "StartNr", "Depot", "Vereniging" ).getBytes());
					}
					if( firstRow ) {
						fs.write(rowPR(FIRST_ROW, 
								res.getString("Cap"),
								cvtString(res.getString("Naam")),
								res.getString("Startnr"),
								res.getString("DepotNr"),
								cvtString(res.getString("Vereniging"))
								).getBytes());
						firstRow = false;
					} else {
						fs.write(rowPR(NORMAL, 
								res.getString("Cap"),
								cvtString(res.getString("Naam")),
								res.getString("Startnr"),
								res.getString("DepotNr"),
								cvtString(res.getString("Vereniging"))
								).getBytes());
					}
				}
			} catch (SQLException e) {
				JOptionPane.showMessageDialog(null,
						e.getMessage()+e.getCause(),
						"SQL fout", JOptionPane.ERROR_MESSAGE);
			}
			if( ! hasBegun ) { SConnection.commit(); }
			//execute query
			fs.write("</w:tbl>".getBytes());
			//end of table
			fs.write(sectPrPR.getBytes());
			fs.write("</wx:sect>".getBytes());
//			fs.write("".getBytes());
			fs.write("</w:body>".getBytes());
			fs.write("</w:wordDocument>".getBytes());
			fs.close();
		} 
		catch(FileNotFoundException e) {
			JOptionPane.showMessageDialog(null,
					e.getMessage(),
					"ExportBoekje", JOptionPane.ERROR_MESSAGE);
		}
		catch(IOException e) {
			JOptionPane.showMessageDialog(null,
					e.getMessage(),
					"ExportBoekje", JOptionPane.ERROR_MESSAGE);
		}
	}

	String mm2twips(int mmval) {
		// 1 cm = 975 twips 
		String mm = null;
		mm = new Integer(new Double(mmval*97.5).intValue()).toString();
		return(mm);
	}
	
	String cell1DV(int type, String value) {
		String content = null;
		content = "<w:tc><w:tcPr><w:tcBorders>";
		if( type == FIRST_ROW )
			content += "<w:top w:val=\"single\" w:sz=\"6\" wx:bdrwidth=\"15\" w:space=\"0\" w:color=\"008000\"/>";
		else if( type == HEADER_ROW )
			content += "<w:bottom w:val=\"single\" w:sz=\"6\" wx:bdrwidth=\"15\" w:space=\"0\" w:color=\"008000\"/>";
		content += "</w:tcBorders></w:tcPr><w:p>";
		content += "<w:pPr><w:rPr><w:sz w:val=\"22\"/></w:rPr></w:pPr>";
		content += "<w:r><w:rPr>";
		if( type == HEADER_ROW ) content+= "<w:b/>";
		content += "<w:sz w:val=\"22\"/></w:rPr>";
		content += "<w:t>"+value+"</w:t></w:r></w:p></w:tc>";
		return (content);
	}
	
	String cell2DV(int type, String value) {
		String content = null;
		content = "<w:tc><w:tcPr><w:tcBorders>";
		if( type == FIRST_ROW )
			content += "<w:top w:val=\"single\" w:sz=\"6\" wx:bdrwidth=\"15\" w:space=\"0\" w:color=\"008000\"/>";
		else if( type == HEADER_ROW )
			content += "<w:bottom w:val=\"single\" w:sz=\"6\" wx:bdrwidth=\"15\" w:space=\"0\" w:color=\"008000\"/>";
		content += "</w:tcBorders></w:tcPr><w:p>";
		content += "<w:pPr>";
		if( type == SUM_ROW ) content += "<w:jc w:val=\"right\"/>";
		content += "<w:rPr><w:sz w:val=\"22\"/></w:rPr></w:pPr>";
		content += "<w:r><w:rPr>";
		if( type == HEADER_ROW ) content+= "<w:b/>";
		content += "<w:sz w:val=\"22\"/></w:rPr>";
		content += "<w:t>"+value+"</w:t></w:r></w:p></w:tc>";
		return (content);
	}
	
	String cell3DV(int type, String value) {
		String content = null;
		content = "<w:tc><w:tcPr><w:tcBorders>";
		if( type == FIRST_ROW )
			content += "<w:top w:val=\"single\" w:sz=\"6\" wx:bdrwidth=\"15\" w:space=\"0\" w:color=\"008000\"/>";
		else if( type == HEADER_ROW )
			content += "<w:bottom w:val=\"single\" w:sz=\"6\" wx:bdrwidth=\"15\" w:space=\"0\" w:color=\"008000\"/>";
		content += "</w:tcBorders></w:tcPr><w:p>";
		content += "<w:pPr><w:jc w:val=\"right\"/><w:rPr><w:sz w:val=\"22\"/></w:rPr></w:pPr>";
		if( type == SUM_ROW ) {
			content += "<w:r><w:rPr>";
			content += "<w:sz w:val=\"22\"/></w:rPr>";
			content += "<w:fldChar w:fldCharType=\"begin\"/></w:r>";
			content += "<w:r><w:rPr>";
			content += "<w:sz w:val=\"22\"/></w:rPr>";
			content += "<w:instrText> =SUM(ABOVE) </w:instrText></w:r>";
			content += "<w:r><w:rPr>";
			content += "<w:sz w:val=\"22\"/></w:rPr>";
			content += "<w:fldChar w:fldCharType=\"separate\"/></w:r>";
		}
		content += "<w:r><w:rPr>";
		if( type == HEADER_ROW ) content+= "<w:b/>";
		content += "<w:sz w:val=\"22\"/></w:rPr>";
		content += "<w:t>"+value+"</w:t></w:r>";
		if( type == SUM_ROW ) {
			content += "<w:r><w:rPr>";
			content += "<w:sz w:val=\"22\"/></w:rPr>";
			content += "<w:fldChar w:fldCharType=\"end\"/></w:r>";
		}
		content += "</w:p></w:tc>";
		return (content);
	}
	
	String rowDV( int type, String col1value, String col2value, String col3value) {
		String rowvalue = null;
		rowvalue = "<w:tr>";
		rowvalue+= this.cell1DV(type, col1value);
		rowvalue+= this.cell2DV(type, col2value);
		rowvalue+= this.cell3DV(type, col3value);
		rowvalue += "</w:tr>";
		return(rowvalue);
	}
	
	String printHeader(String prog, String sufx) {
		String rowvalue = null;
		boolean hasBegun = SConnection.getConnection().hasBegun();
		if( ! hasBegun ) { SConnection.begin(); }
		Connection JDBCcon = SConnection.getBegunDBConnection();
		try {
			String nrStarts = "";
			String titel = "";
			String capKleur = "";
			String capKleurKNZB = "";
			
			Statement stmt = JDBCcon.createStatement();
			ResultSet res = stmt.executeQuery(
					"SELECT COUNT(*) as NrStarts " +
					" FROM start " +
					" WHERE ProgNr=" + prog +
					" AND Suffix='" + sufx + "'"
			);
			if( res.next() ) {
				nrStarts = new Integer(res.getInt("NrStarts")).toString();
			} else {
				JOptionPane.showMessageDialog(null,
						"Geen starts gevonden in " +
						"Programma " + prog + " Suffix " + sufx + "." ,
						"SQL fout", JOptionPane.ERROR_MESSAGE);
				
			}
			res = stmt.executeQuery(
					"SELECT Titel, CapKleur, CapKleurKNZB " +
				    "FROM Programma " +
				    "WHERE ProgNr=" + prog +
				    " AND Suffix='" + sufx + "'"
			);
			if( res.next() ) {
				titel = res.getString("Titel");
				capKleur = res.getString("CapKleur");
				if(capKleur == null) capKleur="[Geen]";
				capKleurKNZB = res.getString("CapKleurKNZB");
			} else {
				JOptionPane.showMessageDialog(null,
						"Programma " + prog + " Suffix " + sufx +
						" is niet gevonden in tabel 'Programma'." ,
						"SQL fout", JOptionPane.ERROR_MESSAGE);
				
			}
			//Regel 1
			rowvalue = "<w:p><w:r><w:br w:type=\"page\"/></w:r></w:p>";
			rowvalue += "<w:p><w:pPr><w:rPr><w:b/></w:rPr></w:pPr><w:r><w:rPr><w:b/></w:rPr>";
			rowvalue += "<w:t>Programma " + prog + sufx + " : " + titel + "</w:t>";
			rowvalue += "</w:r></w:p>";
			//Regel 2
			rowvalue += "<w:p><w:r>";
			rowvalue += "<w:t>(" + nrStarts + " deelnemers, caps ";
			if( capKleur.equalsIgnoreCase(capKleurKNZB) ) {
				rowvalue += capKleur;
			} else {
				rowvalue += capKleur + " en " + capKleurKNZB;
			}
			rowvalue += ", prijzen voor plaatsen " + getPlaatsen(new Integer(prog), sufx);
			rowvalue += ")</w:t>";
			rowvalue += "</w:r></w:p><w:p/>";
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null,
					e.getMessage()+e.getCause(),
					"SQL fout", JOptionPane.ERROR_MESSAGE);
		}
		if( ! hasBegun ) { SConnection.commit(); }
		return(rowvalue);
	}
	
	String cell1PR(int type, String value) {
		String content = null;
		content = "<w:tc><w:tcPr><w:tcBorders>";
		if( type == FIRST_ROW )
			content += "<w:top w:val=\"single\" w:sz=\"6\" wx:bdrwidth=\"15\" w:space=\"0\" w:color=\"00808080\"/>";
		if( type == HEADER_ROW )
			content += "<w:bottom w:val=\"single\" w:sz=\"6\" wx:bdrwidth=\"15\" w:space=\"0\" w:color=\"808080\"/>";
		content += "</w:tcBorders></w:tcPr><w:p>";
		content += "<w:pPr><w:rPr><w:sz w:val=\"22\"/></w:rPr></w:pPr>";
		content += "<w:r><w:rPr>";
		if( type == HEADER_ROW ) content+= "<w:b/>";
		content += "<w:sz w:val=\"22\"/></w:rPr>";
		content += "<w:t>"+value+"</w:t></w:r></w:p></w:tc>";
		return (content);
	}
	
	String cell2PR(int type, String value) {
		String content = null;
		content = "<w:tc><w:tcPr><w:tcBorders>";
		if( type == FIRST_ROW )
			content += "<w:top w:val=\"single\" w:sz=\"6\" wx:bdrwidth=\"15\" w:space=\"0\" w:color=\"808080\"/>";
		if( type == HEADER_ROW )
			content += "<w:bottom w:val=\"single\" w:sz=\"6\" wx:bdrwidth=\"15\" w:space=\"0\" w:color=\"808080\"/>";
		content += "</w:tcBorders></w:tcPr><w:p>";
		content += "<w:pPr><w:rPr><w:sz w:val=\"22\"/></w:rPr></w:pPr>";
		content += "<w:r><w:rPr>";
		if( type == HEADER_ROW ) content+= "<w:b/>";
		content += "<w:sz w:val=\"22\"/></w:rPr>";
		content += "<w:t>"+value+"</w:t></w:r></w:p></w:tc>";
		return (content);
	}
	
	String cell3PR(int type, String value) {
		String content = null;
		content = "<w:tc><w:tcPr><w:tcBorders>";
		if( type == FIRST_ROW )
			content += "<w:top w:val=\"single\" w:sz=\"6\" wx:bdrwidth=\"15\" w:space=\"0\" w:color=\"808080\"/>";
		if( type == HEADER_ROW )
			content += "<w:bottom w:val=\"single\" w:sz=\"6\" wx:bdrwidth=\"15\" w:space=\"0\" w:color=\"808080\"/>";
		content += "</w:tcBorders></w:tcPr><w:p>";
		content += "<w:pPr><w:rPr><w:sz w:val=\"22\"/></w:rPr></w:pPr>";
		content += "<w:r><w:rPr>";
		if( type == HEADER_ROW ) content+= "<w:b/>";
		content += "<w:sz w:val=\"22\"/></w:rPr>";
		content += "<w:t>"+value+"</w:t></w:r></w:p></w:tc>";
		return (content);
	}
	
	String cell4PR(int type, String value) {
		String content = null;
		content = "<w:tc><w:tcPr><w:tcBorders>";
		if( type == FIRST_ROW )
			content += "<w:top w:val=\"single\" w:sz=\"6\" wx:bdrwidth=\"15\" w:space=\"0\" w:color=\"808080\"/>";
		if( type == HEADER_ROW )
			content += "<w:bottom w:val=\"single\" w:sz=\"6\" wx:bdrwidth=\"15\" w:space=\"0\" w:color=\"808080\"/>";
		content += "</w:tcBorders></w:tcPr><w:p>";
		content += "<w:pPr><w:rPr><w:sz w:val=\"22\"/></w:rPr></w:pPr>";
		content += "<w:r><w:rPr>";
		if( type == HEADER_ROW ) content+= "<w:b/>";
		content += "<w:sz w:val=\"22\"/></w:rPr>";
		content += "<w:t>"+value+"</w:t></w:r></w:p></w:tc>";
		return (content);
	}
	
	String cell5PR(int type, String value) {
		String content = null;
		content = "<w:tc><w:tcPr><w:tcBorders>";
		if( type == FIRST_ROW )
			content += "<w:top w:val=\"single\" w:sz=\"6\" wx:bdrwidth=\"15\" w:space=\"0\" w:color=\"808080\"/>";
		if( type == HEADER_ROW )
			content += "<w:bottom w:val=\"single\" w:sz=\"6\" wx:bdrwidth=\"15\" w:space=\"0\" w:color=\"808080\"/>";
		content += "</w:tcBorders></w:tcPr><w:p>";
		content += "<w:pPr><w:rPr><w:sz w:val=\"22\"/></w:rPr></w:pPr>";
		content += "<w:r><w:rPr>";
		if( type == HEADER_ROW ) content+= "<w:b/>";
		content += "<w:sz w:val=\"22\"/></w:rPr>";
		content += "<w:t>"+value+"</w:t></w:r></w:p></w:tc>";
		return (content);
	}
	
	String rowPR( int type, String col1value, String col2value, String col3value, String col4value, String col5value) {
		String rowvalue = null;
		rowvalue = "<w:tr>";
		if( type == HEADER_ROW ) rowvalue += "<w:trPr><w:tblHeader/></w:trPr>";
		rowvalue+= this.cell1PR(type, col1value);
		rowvalue+= this.cell2PR(type, col2value);
		rowvalue+= this.cell3PR(type, col3value);
		rowvalue+= this.cell4PR(type, col4value);
		rowvalue+= this.cell5PR(type, col5value);
		rowvalue += "</w:tr>";
		return(rowvalue);
	}
	
	String getPlaatsen(Integer prognr, String suffix) {
		StatistiekenperprogrammaBean stats = new StatistiekenperprogrammaBean();
		stats.setPrognr(prognr);
		stats.setSuffix(suffix);
		stats.read();
		Long nrprijzen = stats.getNrprijzen();
		String value = "";
	    if( nrprijzen == 1 ) value = "1";
	    if( nrprijzen == 2 ) value = "1 en 2";
	    if( nrprijzen == 3 ) value  = "1, 2 en 3";
	    if( nrprijzen >= 4 ) value = "1 t/m "+nrprijzen;
	    return(value);
	}
	
	String cvtString(String value) {
		String cvtValue = null;
		cvtValue = value.replaceAll("[&]", "&amp;");
		// alle characters > 256 moeten #xFFFF worden
		String newString = "";
		for( int i=0; i<cvtValue.length(); i++ ) {
			if( cvtValue.charAt(i)>127 ) 
				newString+="&#x"+toHex(cvtValue.charAt(i))+";";
			else
				newString+=cvtValue.charAt(i);
		}
		return(newString);
	}
	
	String toHex(int n)	{
		String h = "" ;
		int r=0;
		int nn=n ;
		do {
			r=nn % 16 ;
			nn= nn / 16 ;
			switch (r) {
				case 10: h = "A" + h; break ;
				case 11: h = "B" + h; break ;
				case 12: h = "C" + h; break ;
				case 13: h = "D" + h; break ;
				case 14: h = "E" + h; break ;
				case 15: h = "F" + h; break ;
				default: h = r + h; break ;
			}
		}
		while (nn > 0) ;
		return h ;
	}
}
